﻿Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient

'SI O SI DEBEN INCORPORAR TODOS ESTOS IMPORTS A SUS CLASES DAO QUE AGREGUEN
' SI O SI DEBEN AGREGAR ESTAS TRES LINEAS DE CODIGO PARA TRAER DATOS DESDE LA BD:
' Dim tabla As New Data.DataTable    
' Dim con As String = ConfigurationManager.ConnectionStrings("Conex").ConnectionString
'Dim cn As New SqlConnection(con)

Public Class DAOCliente
   

    '/////////////////////////////BUSCAR/////////////////////////////////

    Public Function DAOBuscarUltimoIDCliente() As Integer
        'metodo para buscar el ultimo nro de cliente y agregar uno nuevo
        Dim id As Integer = 0
        Dim tabla As New Data.DataTable
        Dim con As String = ConfigurationManager.ConnectionStrings("Conex").ConnectionString
        Dim cn As New SqlConnection(con)
       
        Try
            cn.Open()
            Dim sql As New SqlCommand() 'variable para la cadena a mandar al motor
            'cadena
            sql.CommandText = "select MAX(c.ID_CLIENTE) as 'id' from T_CLIENTES c"
            sql.CommandType = CommandType.Text
            sql.Connection = cn
            'vinculo con la conexión
            Dim dr As SqlDataReader = sql.ExecuteReader()
            'creo el datareader con laconsulta
            While dr.Read()
                'recorro el datarreader
                id = Integer.Parse(dr("id").ToString()) + 1

            End While
        Catch
        Finally
            cn.Close()
        End Try

        Return id

    End Function

    Public Function DAOBuscarClienteID(ByVal idCli As Integer) As NEGCliente
        'metodo para buscar todos los datos del cliente
        Dim cli As New NEGCliente()
        Dim tabla As New Data.DataTable
        Dim con As String = ConfigurationManager.ConnectionStrings("Conex").ConnectionString
        Dim cn As New SqlConnection(con)

        Dim sql As String = "SELECT c.ID_CLIENTE,c.RAZON_SOCIAL, c.MAIL, c.TELEFONO, c.OBSERVACIONES,d.CALLE, d.NRO_CALLE, d.PISO, d.DEPTO,"
        sql += " d.COD_POSTAL AS COD_POSTAL,b.ID_BARRIO, l.ID_LOCALIDAD FROM dbo.T_CLIENTES c LEFT OUTER JOIN T_DOMICILIOS d "
        sql += " ON c.ID_DOMICILIO = D.ID_DOMICILIO LEFT OUTER JOIN T_BARRIOS b ON d.ID_BARRIO = b.ID_BARRIO "
        sql += " LEFT OUTER JOIN T_LOCALIDADES l ON B.ID_LOCALIDAD = L.ID_LOCALIDAD WHERE c.ID_CLIENTE = " & idCli

        Dim cmd As New SqlCommand()

        cn.Open()

        cmd.CommandText = sql
        cmd.Connection = cn
        cmd.CommandType = CommandType.Text

        Dim dr As SqlDataReader = cmd.ExecuteReader()
        While dr.Read()
            cli.nroCli = Integer.Parse((dr("ID_CLIENTE")).ToString())
            cli.razonSocial = (dr("RAZON_SOCIAL")).ToString()
            cli.mail = (dr("MAIL")).ToString()
            cli.tel = Integer.Parse((dr("TELEFONO")).ToString())
            cli.observ = (dr("OBSERVACIONES")).ToString()

            cli.calle = (dr("CALLE")).ToString()

            cli.nro = Integer.Parse((dr("NRO_CALLE")).ToString())
            cli.piso = Integer.Parse((dr("PISO")).ToString())
            cli.depto = Integer.Parse((dr("DEPTO")).ToString())
            cli.cp = Integer.Parse((dr("COD_POSTAL")).ToString())
            cli.barrio = Integer.Parse((dr("ID_BARRIO")).ToString())

            cli.localidad = Integer.Parse((dr("ID_LOCALIDAD")).ToString())
        End While
        cn.Close()
        Return cli

    End Function

    Public Function DAOBuscarContacto(ByVal idCon As Integer) As NEGContacto

        Dim conta As New NEGContacto()
        Dim tabla As New Data.DataTable
        Dim con As String = ConfigurationManager.ConnectionStrings("Conex").ConnectionString
        Dim cn As New SqlConnection(con)

        Try
            cn.Open()
            Dim sql As New SqlCommand("contactos_BUSCARPORPK", cn)
            'primero el nombre del sp y despues la conexion
            sql.CommandType = CommandType.StoredProcedure


            sql.Parameters.Add("@ID_CONTACTO", SqlDbType.Int)
            sql.Parameters("@ID_CONTACTO").Value = idCon

            Dim dr As SqlDataReader = sql.ExecuteReader()
            While dr.Read()
                conta.idContacto = Integer.Parse((dr("ID_CONTACTO")).ToString())
                conta.nombre = (dr("NOMBRE")).ToString()
                conta.ape = dr("APELLIDO").ToString()
                conta.descrip = dr("DESCRIPCION").ToString()
                conta.mail = dr("MAIL").ToString()
                conta.tel = Long.Parse((dr("TELEFONO")).ToString())

                conta.observ = (dr("OBSERVACIONES")).ToString()
            End While
        Catch
        Finally
            cn.Close()
        End Try

        Return conta
    End Function



    Public Function DAOcargarTextBoxCliente() As SqlDataReader 'buscar clientes al ir ingresando el nombre o el codigoCli en el pedido
        Dim tabla As New Data.DataTable
        Dim con As String = ConfigurationManager.ConnectionStrings("Conex").ConnectionString
        Dim cn As New SqlConnection(con)

        Dim sql As String = "select c.ID_CLIENTE, c.RAZON_SOCIAL from T_CLIENTES c"

        cn.Open()
        Dim cmd As New SqlCommand()

        cmd.Connection = cn
        cmd.CommandText = sql
        cmd.CommandType = CommandType.Text

        Dim dr As SqlDataReader = cmd.ExecuteReader()
        'no cerrar la conexión al hacer el autocompletar
        Return dr
    End Function

    Public Function buscarNombreCliente(ByVal idCli As Integer) As String
        'metodo que solo busca el nombre del cliente pasandole el idCliente
        Dim nombreCli As String = ""
        Dim tabla As New Data.DataTable
        Dim con As String = ConfigurationManager.ConnectionStrings("Conex").ConnectionString
        Dim cn As New SqlConnection(con)
     
        Dim sql As String = "select c.ID_CLIENTE, c.RAZON_SOCIAL from T_CLIENTES c WHERE c.ID_CLIENTE= " & idCli

        cn.Open()
        Dim cmd As New SqlCommand()

        cmd.Connection = cn
        cmd.CommandText = sql
        cmd.CommandType = CommandType.Text

        Dim dr As SqlDataReader = cmd.ExecuteReader()

        While dr.Read()
            nombreCli = dr("RAZON_SOCIAL").ToString()
        End While

        cn.Close()
        Return nombreCli
    End Function

    Public Function buscarIdCliente(ByVal razonSoc As String) As String
        'buscar el id al seleccionar la razon social
        Dim nombreCli As String = ""
        Dim tabla As New Data.DataTable
        Dim con As String = ConfigurationManager.ConnectionStrings("Conex").ConnectionString
        Dim cn As New SqlConnection(con)

        Dim sql As String = "select c.ID_CLIENTE, c.RAZON_SOCIAL from T_CLIENTES c WHERE c.RAZON_SOCIAL = '" & razonSoc & "'"

        cn.Open()
        Dim cmd As New SqlCommand()

        cmd.Connection = cn
        cmd.CommandText = sql
        cmd.CommandType = CommandType.Text

        Dim dr As SqlDataReader = cmd.ExecuteReader()

        While dr.Read()
            nombreCli = dr("ID_CLIENTE").ToString()
        End While

        cn.Close()
        Return nombreCli
    End Function

    Public Function buscarClienteXNombre(ByVal nombre As String) As Data.DataTable
        'metodo para filtrar clientes por el nombre
        Dim tabla As New Data.DataTable
        Dim con As String = ConfigurationManager.ConnectionStrings("Conex").ConnectionString
        Dim cn As New SqlConnection(con)
        Dim sql As String = ""

        If nombre = "" Then
            'pregunto si nombre viene vacio, es xq busco todos los clientes cargados
            sql = "select c.ID_CLIENTE, c.RAZON_SOCIAL,c.TELEFONO, d.ID_BARRIO, b.NOMBRE "
            sql += " from T_CLIENTES c left join T_DOMICILIOS d on c.ID_DOMICILIO=d.ID_DOMICILIO join T_BARRIOS b on d.ID_BARRIO=b.ID_BARRIO"
        Else

            ' si no viene vacio busco por ese nombre
            sql = "select c.ID_CLIENTE, c.RAZON_SOCIAL,c.TELEFONO, d.ID_BARRIO, b.NOMBRE "
            sql += " from T_CLIENTES c left join T_DOMICILIOS d on c.ID_DOMICILIO=d.ID_DOMICILIO join T_BARRIOS b on d.ID_BARRIO=b.ID_BARRIO"
            sql += " Where c.RAZON_SOCIAL LIKE '%" & nombre & "%'"
        End If

        cn.Open()
        Dim cmd As New SqlCommand()

        cmd.Connection = cn
        cmd.CommandText = sql
        cmd.CommandType = CommandType.Text


        tabla.Load(cmd.ExecuteReader())

        cn.Close()

        Return tabla
    End Function

    '////////////////////////////CARGAR GRILLA//////////////////

    Public Function cargarGrillaCliente() As Data.DataTable
        Dim tabla As New Data.DataTable
        Dim con As String = ConfigurationManager.ConnectionStrings("Conex").ConnectionString
        Dim cn As New SqlConnection(con)
      
        Dim sql As String = "select c.ID_CLIENTE, c.RAZON_SOCIAL,c.TELEFONO, d.ID_BARRIO, b.NOMBRE "
        sql += " from T_CLIENTES c left join T_DOMICILIOS d on c.ID_DOMICILIO=d.ID_DOMICILIO join T_BARRIOS b on d.ID_BARRIO=b.ID_BARRIO"
        Try
            cn.Open()
            Dim cmd As New SqlCommand()

            cmd.Connection = cn
            cmd.CommandText = sql
            cmd.CommandType = CommandType.Text

            tabla.Load(cmd.ExecuteReader())
        Catch ex As Exception
        Finally
            cn.Close()
        End Try

        Return tabla
    End Function

    'carga la grilla con los contactos
    Public Function cargarGrillaContacto(ByVal idCliente As Integer) As Data.DataTable
        Dim tabla As New Data.DataTable
        Dim con As String = ConfigurationManager.ConnectionStrings("Conex").ConnectionString
        Dim cn As New SqlConnection(con)
      
        Dim sql As String = "select con.* from T_CONTACTOS con join T_CLIENTES cli on con.ID_CLIENTE=cli.ID_CLIENTE where cli.ID_CLIENTE= @idCliente "
        Try
            cn.Open()
            Dim cmd As New SqlCommand()
            cmd.Parameters.AddWithValue("@idCliente", idCliente)
            cmd.Connection = cn
            cmd.CommandText = sql
            cmd.CommandType = CommandType.Text

            tabla.Load(cmd.ExecuteReader())
        Catch ex As Exception
        Finally
            cn.Close()
        End Try
       
        Return tabla
    End Function

    '////////////////////////ELIMINAR///////////////////////////

    Public Sub eliminarContacto(ByVal id As Integer)
        Dim tabla As New Data.DataTable
        Dim con As String = ConfigurationManager.ConnectionStrings("Conex").ConnectionString
        Dim cn As New SqlConnection(con)

        Try
            cn.Open()
            Dim sql As New SqlCommand()
            'variable para la cadena a mandar al motor
            'cadena
            sql.CommandText = "delete T_CONTACTOS where ID_CONTACTO = " & id
            sql.CommandType = CommandType.Text
            sql.Connection = cn
            'vinculo con la conexión

            sql.ExecuteNonQuery()
        Catch
        Finally
            cn.Close()
        End Try
    End Sub

    Public Sub eliminarCliente(ByVal id As Integer)
        Dim tabla As New Data.DataTable
        Dim con As String = ConfigurationManager.ConnectionStrings("Conex").ConnectionString
        Dim cn As New SqlConnection(con)

        Try
            cn.Open()
            Dim sql As New SqlCommand()
            'variable para la cadena a mandar al motor
            'cadena
            sql.CommandText = "delete T_CONTACTOS where ID_CLIENTE = " & id
            sql.CommandType = CommandType.Text
            sql.Connection = cn
            'vinculo con la conexión
            sql.ExecuteNonQuery()

            sql.CommandText = "delete T_CLIENTES where ID_CLIENTE = " & id
            sql.CommandType = CommandType.Text
            sql.Connection = cn
            'vinculo con la conexión

            sql.ExecuteNonQuery()
        Catch
        Finally
            cn.Close()
        End Try
    End Sub

    '//////////////////////////GUARDAR//////////////////////////

    Public Function DAOInsertCliente(ByVal cli As NEGCliente) As Boolean
        ' inserto el cliente con su domicilio
        Dim band As Boolean = False
        'bandera para ver si la transaccion termina bien o no
        Dim tabla As New Data.DataTable
        Dim con As String = ConfigurationManager.ConnectionStrings("Conex").ConnectionString
        Dim cn As New SqlConnection(con)

        Try
            cn.Open()
            Dim sql As New SqlCommand("clientes_INSERTAR", cn)
            'primero el nombre del sp y despues la conexion
            sql.CommandType = CommandType.StoredProcedure

            sql.Parameters.Add("@RAZON_SOCIAL", SqlDbType.NVarChar, 50)
            sql.Parameters("@RAZON_SOCIAL").Value = cli.razonSocial

            sql.Parameters.Add("@TELEFONO", SqlDbType.NChar, 15)
            sql.Parameters("@TELEFONO").Value = cli.tel

            sql.Parameters.Add("@MAIL", SqlDbType.NVarChar, 50)
            sql.Parameters("@MAIL").Value = cli.mail


            sql.Parameters.Add("@OBSERVACIONES", SqlDbType.NVarChar, 50)
            sql.Parameters("@OBSERVACIONES").Value = cli.observ

            sql.Parameters.Add("@CALLE", SqlDbType.NVarChar, 50)
            sql.Parameters("@CALLE").Value = cli.calle

            sql.Parameters.Add("@NRO_CALLE", SqlDbType.Int, 4)
            sql.Parameters("@NRO_CALLE").Value = cli.nro

            sql.Parameters.Add("@PISO", SqlDbType.Int, 4)
            sql.Parameters("@PISO").Value = cli.piso

            sql.Parameters.Add("@DEPTO", SqlDbType.Int, 4)
            sql.Parameters("@DEPTO").Value = cli.piso

            sql.Parameters.Add("@COD_POSTAL", SqlDbType.Int, 4)
            sql.Parameters("@COD_POSTAL").Value = cli.cp

            sql.Parameters.Add("@ID_BARRIO", SqlDbType.Int, 4)
            sql.Parameters("@ID_BARRIO").Value = cli.barrio

            sql.Parameters.Add("@NRO_ING_BRUTOS", SqlDbType.Int)
            sql.Parameters("@NRO_ING_BRUTOS").Value = cli.ingBrutos

            sql.Parameters.Add("@ID_COND_IVA", SqlDbType.Int)
            sql.Parameters("@ID_COND_IVA").Value = cli.idCondIva

            sql.Parameters.Add("@CUIT", SqlDbType.NChar, 13)
            sql.Parameters("@CUIT").Value = cli.cuit

            If cli.ctaCte = 0 Then
                sql.Parameters.Add("@IDCUENTA", SqlDbType.Int)
                sql.Parameters("@IDCUENTA").Value = DBNull.Value
            Else
                sql.Parameters.Add("@IDCUENTA", SqlDbType.Int)
                sql.Parameters("@IDCUENTA").Value = cli.ctaCte
            End If

            'sql.Parameters.Add("@LIMITE_CRE", SqlDbType.Int)
            'sql.Parameters("@LIMITE_CRE").Value = cli.limiteCred

            sql.ExecuteNonQuery()


            band = True
        Catch e As Exception
            band = False
        Finally
            cn.Close()
        End Try
        Return band
    End Function

    Public Function DAOInsertarContactosClie(ByVal contacto As NEGContacto, ByVal idCli As Integer) As Boolean
        Dim band As Boolean = False         'bandera para ver si la transaccion termina bien o no
        Dim tabla As New Data.DataTable
        Dim con As String = ConfigurationManager.ConnectionStrings("Conex").ConnectionString
        Dim cn As New SqlConnection(con)

        Try
            cn.Open()
            Dim sql As New SqlCommand("contactos_INSERTAR", cn)
            'primero el nombre del sp y despues la conexion
            sql.CommandType = CommandType.StoredProcedure

            sql.Parameters.Add("@NOMBRE", SqlDbType.NVarChar, 50)
            sql.Parameters("@NOMBRE").Value = contacto.nombre

            sql.Parameters.Add("@APELLIDO", SqlDbType.NVarChar, 50)
            sql.Parameters("@APELLIDO").Value = contacto.ape

            sql.Parameters.Add("@DESCRIPCION", SqlDbType.NVarChar, 50)
            sql.Parameters("@DESCRIPCION").Value = contacto.descrip

            sql.Parameters.Add("@MAIL", SqlDbType.NVarChar, 50)
            sql.Parameters("@MAIL").Value = contacto.mail

            sql.Parameters.Add("@TELEFONO", SqlDbType.NChar, 15)
            sql.Parameters("@TELEFONO").Value = contacto.tel

            sql.Parameters.Add("@OBSERVACIONES", SqlDbType.NVarChar, 50)
            sql.Parameters("@OBSERVACIONES").Value = contacto.observ

            sql.Parameters.Add("@ID_CLIENTE", SqlDbType.Int, 10)
            sql.Parameters("@ID_CLIENTE").Value = idCli


            sql.ExecuteNonQuery()

            band = True
        Catch e As Exception
            band = False
        Finally
            cn.Close()
        End Try
        Return band
    End Function


    '//////////////////////////////MODIFICAR/////////////////////////

    Public Function DAOModificarCliente(ByVal cli As NEGCliente, ByVal nroCli As Integer) As Boolean
        Dim band As Boolean = False
        'bandera para ver si la transaccion termina bien o no
        Dim tabla As New Data.DataTable
        Dim con As String = ConfigurationManager.ConnectionStrings("Conex").ConnectionString
        Dim cn As New SqlConnection(con)
        Try
            cn.Open()
            Dim sql As New SqlCommand("clientes_EDITAR", cn)
            'primero el nombre del sp y despues la conexion
            sql.CommandType = CommandType.StoredProcedure


            sql.Parameters.Add("ID_CLIENTE", SqlDbType.NVarChar, 50)
            sql.Parameters("ID_CLIENTE").Value = nroCli

            sql.Parameters.Add("@CALLE", SqlDbType.NVarChar, 50)
            sql.Parameters("@CALLE").Value = cli.calle
            sql.Parameters.Add("@NRO_CALLE", SqlDbType.Int, 4)
            sql.Parameters("@NRO_CALLE").Value = cli.nro

            sql.Parameters.Add("@PISO", SqlDbType.Int, 4)
            sql.Parameters("@PISO").Value = cli.piso

            sql.Parameters.Add("@DEPTO", SqlDbType.Int, 4)
            sql.Parameters("@DEPTO").Value = cli.piso

            sql.Parameters.Add("@ID_BARRIO", SqlDbType.Int, 4)
            sql.Parameters("@ID_BARRIO").Value = cli.barrio

            sql.Parameters.Add("@COD_POSTAL", SqlDbType.Int, 4)
            sql.Parameters("@COD_POSTAL").Value = cli.cp

            sql.Parameters.Add("@RAZON_SOCIAL", SqlDbType.NVarChar, 50)
            sql.Parameters("@RAZON_SOCIAL").Value = cli.razonSocial

            sql.Parameters.Add("@TELEFONO", SqlDbType.NChar, 15)
            sql.Parameters("@TELEFONO").Value = cli.tel

            sql.Parameters.Add("@MAIL", SqlDbType.NVarChar, 50)
            sql.Parameters("@MAIL").Value = cli.mail

            sql.Parameters.Add("@FAX", SqlDbType.NChar, 15)
            sql.Parameters("@FAX").Value = DBNull.Value

            sql.Parameters.Add("@OBSERVACIONES", SqlDbType.NVarChar, 50)
            sql.Parameters("@OBSERVACIONES").Value = cli.observ

            sql.Parameters.Add("@NRO_ING_BRUTOS", SqlDbType.Int)
            sql.Parameters("@NRO_ING_BRUTOS").Value = cli.ingBrutos

            sql.Parameters.Add("@ID_COND_IVA", SqlDbType.Int)
            sql.Parameters("@ID_COND_IVA").Value = cli.idCondIva

            sql.Parameters.Add("@CUIT", SqlDbType.NChar, 13)
            sql.Parameters("@CUIT").Value = cli.cuit

            If cli.ctaCte = 0 Then
                sql.Parameters.Add("@IDCUENTA", SqlDbType.Int)
                sql.Parameters("@IDCUENTA").Value = DBNull.Value
            Else
                sql.Parameters.Add("@IDCUENTA", SqlDbType.Int)
                sql.Parameters("@IDCUENTA").Value = cli.ctaCte
            End If

            sql.ExecuteNonQuery()


            band = True
        Catch e As Exception
            band = False
        Finally
            cn.Close()
        End Try
        Return band
    End Function



    Public Function DAOModificarContacto(ByVal contacto As NEGContacto, ByVal idConta As Integer, ByVal idCliente As Integer) As Boolean
        Dim band As Boolean = False
        'bandera para ver si la transaccion termina bien o no
        Dim tabla As New Data.DataTable
        Dim con As String = ConfigurationManager.ConnectionStrings("Conex").ConnectionString
        Dim cn As New SqlConnection(con)

        Try
            cn.Open()
            Dim sql As New SqlCommand("contactos_EDITAR", cn)
            'primero el nombre del sp y despues la conexion
            sql.CommandType = CommandType.StoredProcedure

            sql.Parameters.Add("@ID_CONTACTO", SqlDbType.NVarChar, 50)
            sql.Parameters("@ID_CONTACTO").Value = idConta

            sql.Parameters.Add("@NOMBRE", SqlDbType.NVarChar, 50)
            sql.Parameters("@NOMBRE").Value = contacto.nombre

            sql.Parameters.Add("@APELLIDO", SqlDbType.NVarChar, 50)
            sql.Parameters("@APELLIDO").Value = contacto.ape

            sql.Parameters.Add("@DESCRIPCION", SqlDbType.NVarChar, 50)
            sql.Parameters("@DESCRIPCION").Value = contacto.descrip

            sql.Parameters.Add("@MAIL", SqlDbType.NVarChar, 50)
            sql.Parameters("@MAIL").Value = contacto.mail

            sql.Parameters.Add("@TELEFONO", SqlDbType.NChar, 15)
            sql.Parameters("@TELEFONO").Value = contacto.tel

            sql.Parameters.Add("@OBSERVACIONES", SqlDbType.NVarChar, 50)
            sql.Parameters("@OBSERVACIONES").Value = contacto.observ

            sql.Parameters.Add("@ID_CLIENTE", SqlDbType.Int, 10)
            sql.Parameters("@ID_CLIENTE").Value = idCliente

            sql.ExecuteNonQuery()


            band = True
            'probando si se sobreescribe en en tortoice
        Catch e As Exception
            band = False
        Finally
            cn.Close()
        End Try
        Return band
    End Function

    'probando si se sobreescribe esto

End Class
